package cn.ainit.sys.models;


import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.ainit.sys.common.dao.BusinessBaseDao;
import cn.ainit.sys.print.XLSXCovertCSVReader;
import cn.ainit.sys.utils.UUID32;
import org.springframework.util.StringUtils;

public class ImportExcel {

	public static String h_id_match=null;
	/**
	 * 
	 * @param url
	 *            传入文件路径
	 * @param tableName
	 *            数据库表名(要导入的)
	 * @param Stirng
	 *            [] clellName 数据库列明
	 * @param int [] num excel下标(从1开始)
	 * @return sql语句集合
	 * 
	 * @throws FileNotFoundException
	 */
	public static List<String> importEmployeeByPoi(InputStream stream,String url,
			String tableName, List lists)
			throws FileNotFoundException {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		try {
			if (url.endsWith(".xls"))
				version = "version2003";
			else if (url.endsWith(".xlsx"))
				version = "version2007";
			//测试
			Workbook hwb = null;
			if (version.equals("version2003")) {
				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			Row row = null;
			// 日期格式化
			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
			h_id_match=UUID32.newGuid();
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				sheet = hwb.getSheetAt(i);
				List<String>nameList=new ArrayList<String>();
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
					row = sheet.getRow(j);
					//取到商品信息的件册数
					/***
					 * 如果导入的Excel数据汇中出现第一列的书号为空时，跳出读取excel的循环，不进行下面数据的读取（规则，excel中的数据不允许出现断行）
					 */
					if(row.getCell(0)==null||"".equals(row.getCell(0).toString().trim())||"".equals(getCellValue(row.getCell(0)))){
						break;
					}
					String h_isbn = getCellValue(row.getCell(0));
					String H_PRICE = getCellValue(row.getCell(2));
					String sqlhid = "SELECT BP.H_ID FROM BS_PRODUCT BP WHERE BP.H_ISBN = '"+h_isbn+"'";
					List<Map<String, Object>> H_ID = BusinessBaseDao.jdbcTemplate.queryForList(sqlhid);
					String hPackagePrice=null;
					if(H_ID.size()>0){
						String H_PACKAGE_PIECE = "SELECT s.H_PACKAGE_PIECE FROM bs_product s WHERE s.h_id = '"+H_ID.get(0).get("H_ID").toString()+"'";
						List<Map<String, Object>> H_P = BusinessBaseDao.jdbcTemplate.queryForList(H_PACKAGE_PIECE);
						Object ob=H_P.get(0).get("H_PACKAGE_PIECE");
						if (ob==null) {
							hPackagePrice="";
						}else {
							hPackagePrice=ob.toString();
						}
						if(row==null ||row.getCell(0)==null || "".equals(row.getCell(0).toString())){
							System.out.println(row.getCell(0));
							break;
						}
					}
					//					String sql = "insert into " + tableName + " (H_PACKAGE_PIECE,SORT_NO,h_inputdate,PICI,";
					//					for (int n = 0; n < lists.size(); n++) {
					//						sql += "" + lists.get(n) + "" + ",";
					//					}
					//					sql = sql.substring(0, sql.length() - 1);
					//					sql += ") values ("+hPackagePrice+","+j+",'"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"',";

					String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,";
					for (int n = 0; n < lists.size(); n++) {						
						sql += "" + lists.get(n) + "" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"',"+j+",";
					//获取行
					if (j==0) {
						for (int j2 = 0; j2 <6; j2++) {		
							if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) {
								nameList.add(getCellValue(row.getCell(j2)));
							}else {
								break;
							}
						}
						continue;
					}
					for (int s = 0; s < lists.size(); s++) {
						sql += "\'" + getCellValue(row.getCell(s))
						+ "\'" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ")";
					System.out.println(sql);
					// sql语句放入集合
					sqls.add(sql);
				}
			}
		} catch (IOException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		return sqls;
	}
	public static List<String> importEmployeeByPoiCw(InputStream stream,String url,
			String tableName, List lists)
			throws FileNotFoundException {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		try {
			if (url.endsWith(".xls"))
				version = "version2003";
			else if (url.endsWith(".xlsx"))
				version = "version2007";
			//测试
			Workbook hwb = null;
			if (version.equals("version2003")) {
				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			Row row = null;
			// 日期格式化
			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
			h_id_match=UUID32.newGuid();
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				sheet = hwb.getSheetAt(i);
				List<String>nameList=new ArrayList<String>();
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
					row = sheet.getRow(j);
					//取到商品信息的件册数
					/***
					 * 如果导入的Excel数据汇中出现第一列的书号为空时，跳出读取excel的循环，不进行下面数据的读取（规则，excel中的数据不允许出现断行）
					 */
					if(row.getCell(0)==null||"".equals(row.getCell(0).toString().trim())||"".equals(getCellValue(row.getCell(0)))){
						break;
					}
					String h_isbn = getCellValue(row.getCell(0));
					String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,IS_PP,";
					for (int n = 0; n < lists.size(); n++) {						
						sql += "" + lists.get(n) + "" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"','"+j+"','"+"0"+"',";
					//获取行
					if (j==0) {
						for (int j2 = 0; j2 <6; j2++) {		
							if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) {
								nameList.add(getCellValue(row.getCell(j2)));
							}else {
								break;
							}
						}
						continue;
					}
					for (int s = 0; s < lists.size(); s++) {
						sql += "\'" + getCellValue(row.getCell(s))
						+ "\'" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ")";
					System.out.println(sql);
					// sql语句放入集合
					sqls.add(sql);
				}
			}
		} catch (IOException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		return sqls;
	}

	public static List<String> importEmployeeByPoiZT(InputStream stream,String url,
													 String tableName, List lists)
			throws FileNotFoundException {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		try {
			if (url.endsWith(".xls"))
				version = "version2003";
			else if (url.endsWith(".xlsx"))
				version = "version2007";
			//测试
			Workbook hwb = null;
			if (version.equals("version2003")) {
				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			Row row = null;
			// 日期格式化
			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
			h_id_match=UUID32.newGuid();
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				sheet = hwb.getSheetAt(i);
				List<String>nameList=new ArrayList<String>();
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
					row = sheet.getRow(j);
					if(isNullRow(row, lists)){
						continue;
					}
					//取到商品信息的件册数
					String h_isbn = getCellValue(row.getCell(0));
					String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,IS_PP,";
					for (int n = 0; n < lists.size(); n++) {
						sql += "" + lists.get(n) + "" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"','"+j+"','"+"0"+"',";
					//获取行
					if (j==0) {
						for (int j2 = 0; j2 <6; j2++) {
							if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) {
								nameList.add(getCellValue(row.getCell(j2)));
							}else {
								break;
							}
						}
						continue;
					}
					for (int s = 0; s < lists.size(); s++) {
						sql += "\'" + getCellValue(row.getCell(s))
								+ "\'" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ")";
					System.out.println(sql);
					// sql语句放入集合
					sqls.add(sql);
				}
			}
		} catch (IOException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		return sqls;
	}

	private static boolean isNullRow(Row row, List lists) {
		if(null == row) {
			return true;
		}
		boolean flag = true;
		for (int s = 0; s < lists.size(); s++) {
			if(StringUtils.hasText(getCellValue(row.getCell(s)))) {
				flag = flag && false;
			}
		}
		return flag;

	}

	public static List<String> importEmployeeByPoiCYXH(InputStream stream,String url,
													 String tableName, List lists)
			throws FileNotFoundException {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		try {
			if (url.endsWith(".xls"))
				version = "version2003";
			else if (url.endsWith(".xlsx"))
				version = "version2007";
			//测试
			Workbook hwb = null;
			if (version.equals("version2003")) {
				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			Row row = null;
			// 日期格式化
			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
			h_id_match=UUID32.newGuid();
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				sheet = hwb.getSheetAt(i);
				List<String>nameList=new ArrayList<String>();
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
					row = sheet.getRow(j);
					//取到商品信息的件册数
					String h_isbn = getCellValue(row.getCell(0));
					String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,IS_PP,";
					for (int n = 0; n < lists.size(); n++) {
						sql += "" + lists.get(n) + "" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"','"+j+"','"+"0"+"',";
					//获取行
					if (j==0) {
						for (int j2 = 0; j2 <6; j2++) {
							if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) {
								nameList.add(getCellValue(row.getCell(j2)));
							}else {
								break;
							}
						}
						continue;
					}
					for (int s = 0; s < lists.size(); s++) {
						sql += "\'" + getCellValue(row.getCell(s))
								+ "\'" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ")";
					System.out.println(sql);
					// sql语句放入集合
					sqls.add(sql);
				}
			}
		} catch (IOException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		return sqls;
	}
	
	public static List<String>importEmployeeByPoiFee(InputStream stream,String url,
			String tableName, List lists)
			throws FileNotFoundException {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		try {
			if (url.endsWith(".xls"))
				version = "version2003";
			else if (url.endsWith(".xlsx"))
				version = "version2007";
			//测试
			Workbook hwb = null;
			if (version.equals("version2003")) {
				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			Row row = null;
			// 日期格式化
			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
			h_id_match=UUID32.newGuid();
			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				sheet = hwb.getSheetAt(i);
				List<String>nameList=new ArrayList<String>();
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
					row = sheet.getRow(j);
					//取到商品信息的件册数
					/***
					 * 如果导入的Excel数据汇中出现第一列的书号为空时，跳出读取excel的循环，不进行下面数据的读取（规则，excel中的数据不允许出现断行）
					 */
					if(row.getCell(0)==null||"".equals(row.getCell(0).toString().trim())||"".equals(getCellValue(row.getCell(0)))){
						break;
					}
					String h_isbn = getCellValue(row.getCell(0));
                    String H_PRICE = getCellValue(row.getCell(2));
                    String sqlhid = "SELECT BP.H_ID FROM BS_PRODUCT BP WHERE BP.H_ISBN = '"+h_isbn+"'";
                    List<Map<String, Object>> H_ID = BusinessBaseDao.jdbcTemplate.queryForList(sqlhid);
                    String hPackagePrice=null;
                    if(H_ID.size()>0){
                        String H_PACKAGE_PIECE = "SELECT s.H_PACKAGE_PIECE FROM bs_product s WHERE s.h_id = '"+H_ID.get(0).get("H_ID").toString()+"'";
                        List<Map<String, Object>> H_P = BusinessBaseDao.jdbcTemplate.queryForList(H_PACKAGE_PIECE);
                        Object ob=H_P.get(0).get("H_PACKAGE_PIECE");
                        if (ob==null) {
                            hPackagePrice="";
                        }else {
                            hPackagePrice=ob.toString();
                        }
                        if(row==null ||row.getCell(0)==null || "".equals(row.getCell(0).toString())){
                            System.out.println(row.getCell(0));
                            break;
                        }
                    }
					String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,IS_PP,";
					for (int n = 0; n < lists.size(); n++) {		
						/*System.out.println(lists.get(n));*/
						sql += "" + lists.get(n) + "" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"','"+j+"','"+"0"+"',";
					//获取行
					if (j==0) {
						for (int j2 = 0; j2 <6; j2++) {		
							if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) { 
								nameList.add(getCellValue(row.getCell(j2)));
							}else {
								break;
							}
						}
						continue;
					}
					for (int s = 0; s < lists.size(); s++) {
						sql += "\'" + getCellValue(row.getCell(s))
						+ "\'" + ",";
					}
					sql = sql.substring(0, sql.length() - 1);
					sql += ")";
					System.out.println(sql);
					// sql语句放入集合
					sqls.add(sql);
				}
			}
		} catch (IOException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		return sqls;
	}
	//	public static  List<String> importEmployeeByPoi(InputStream stream,String url,
	//			String tableName, List lists)
	//			throws Exception {
	//		// 这里是解析出来的Excel的数据存放的List集合
	//		List<String> sqls = new ArrayList<String>();
	//		String version = "";
	//		Workbook hwb = null;
	//		try {
	//
	//			if (url.endsWith(".xls") || url.endsWith(".XLS"))
	//				version = "version2003";
	//			else if (url.endsWith(".xlsx") || url.endsWith(".XLSX"))
	//				version = "version2007";
	//			if (version.equals("version2003")) {
	//				hwb = new HSSFWorkbook(stream);
	//			} else if (version.equals("version2007")) {
	//				hwb = new XSSFWorkbook(stream);
	//			}
	//
	//			// 得到第一个工作表
	//			Sheet sheet = hwb.getSheetAt(0);
	//			Row row = null;
	//			// 日期格式化
	//			DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
	//			h_id_match=UUID32.newGuid();
	//			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
	//				sheet = hwb.getSheetAt(0);
	//				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
	//				for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
	//					row = sheet.getRow(j);
	//					//取到商品信息的件册数
	//					/***
	//					 * 如果导入的Excel数据汇中出现第一列的书号为空时，跳出读取excel的循环，不进行下面数据的读取（规则，excel中的数据不允许出现断行）
	//					 */
	//					if(row.getCell(0)==null||"".equals(row.getCell(0).toString().trim())||"".equals(getCellValue(row.getCell(0)))){
	//						break;
	//					}
	//					String h_isbn = getCellValue(row.getCell(0));
	//					String H_PRICE = getCellValue(row.getCell(2));
	//					String sqlhid = "SELECT BP.H_ID FROM BS_PRODUCT BP WHERE BP.H_ISBN = '"+h_isbn+"'";
	//					System.out.println(sqlhid);
	//					List<Map<String, Object>> H_ID = BusinessBaseDao.jdbcTemplate.queryForList(sqlhid);
	//					String hPackagePrice=null;
	//					if(H_ID.size()>0){
	//						String H_PACKAGE_PIECE = "SELECT s.H_PACKAGE_PIECE FROM bs_product s WHERE s.h_id = '"+H_ID.get(0).get("H_ID").toString()+"'";
	//						System.out.println(H_PACKAGE_PIECE);
	//						List<Map<String, Object>> H_P = BusinessBaseDao.jdbcTemplate.queryForList(H_PACKAGE_PIECE);
	//						System.out.println(H_P.get(0).get("H_PACKAGE_PIECE").toString());
	//						hPackagePrice=H_P.get(0).get("H_PACKAGE_PIECE").toString();
	//						if(row==null ||row.getCell(0)==null || "".equals(row.getCell(0).toString())){
	//							break;
	//						}
	//					}
	//					String sql = "insert into " + tableName + " (H_PACKAGE_PIECE,SORT_NO,h_inputdate,PICI,";
	//					for (int n = 0; n < lists.size(); n++) {
	//						sql += "" + lists.get(n) + "" + ",";
	//					}
	//					sql = sql.substring(0, sql.length() - 1);
	//					sql += ") values ("+hPackagePrice+","+j+",'"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"',";
	//					
	//					for (int s = 0; s < lists.size(); s++) {			 
	//					sql += "\'" + getCellValue(row.getCell(s))+ "\'" + ",";
	//					}
	//
	//					sql = sql.substring(0, sql.length() - 1);
	//					sql += ")";
	//					// sql语句放入集合
	//					sqls.add(sql);
	//				}
	//			 
	//		} catch (IOException e) {
	//			e.printStackTrace();
	//		}finally{
	//			stream.close();
	//		}
	//		return sqls;
	//	}
	////优化从getImprotInfo--getReturnImproInfo导入导出方法
	public static List<String> importEmployeeByPoifordan(InputStream stream,String url,
			String tableName, List lists)
			throws Exception {
		// 这里是解析出来的Excel的数据存放的List集合
		List<String> sqls = new ArrayList<String>();
		String version = "";
		List<String[]> list=new ArrayList<String[]>();
		SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			list = XLSXCovertCSVReader  
			.readerExcel(  
					url,  
					"Sheet1", 8);
		} catch (Exception e) {
			System.out.println(e);
			e.printStackTrace();
		}
		h_id_match=UUID32.newGuid();
		for (int x=0;x<list.size();x++) {
			if (x==0) {
				continue;
			}
			String []record=list.get(x);
			String sql = "insert into " + tableName + " ( h_inputdate ,PICI,";
			for (int n = 0; n < lists.size(); n++) {
				sql += "" + lists.get(n) + "" + ",";
			}
			sql = sql.substring(0, sql.length() - 1);
			sql += ") values ( '"+simpleDateFormat.format(new Date())+"','"+h_id_match+"',";
			for (int s = 0; s < lists.size(); s++) {

				String cellString="";
				if (record[s]==null||"null".equals(record[s])||"".equals(record[s])) {

				}else {
					try {
						cellString=record[s].toString();
						if (s==2) {
							int le=cellString.length();
							if (le>1) {
								cellString=cellString.replace(cellString.charAt(0), '0');
							}
						}
					} catch (Exception e) {
						cellString=record[s].toString();
					}		
				}
				sql += "\'" + cellString
				+ "\'" + ",";
			}
			sql = sql.substring(0, sql.length() - 1);
			sql += ")";
			// sql语句放入集合
			sqls.add(sql);
		}
		return sqls;
	}
	// 判断从Excel文件中解析出来数据的格式
	private static String getCellValue(Cell cell) {
		String value = null;
		if(cell==null){
			return "";
		}
		// 简单的查检列类型
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:// 字符串
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:// 数字
			double dd =  cell.getNumericCellValue();
			BigDecimal bd = new BigDecimal(dd);
			if(bd.toString().indexOf(".")!=-1){
				value = bd.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue()+"";
			}else{
				value = bd.toString();
			}
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_BOOLEAN:// boolean型值
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			break;
		}
		return value;
	}
	/*************************************************
	 * 传入InputStream,解析成Excel,然后导入数据库执行存储过程,获取返回值
	 * 2015年11月26日15:59:34 刘振涛
	 * @param stream excel 二进制流
	 * @param fileName excel文件名
	 * @return
	 */
	public static String ImpExcelToDbAndProc(InputStream stream,String fileName,String dsid,List<String> sql){
		String version="";
		try{
			//判断版本
			if (fileName.endsWith(".xls")|| fileName.endsWith(".XLS"))
				version = "version2003";
			else if (fileName.endsWith(".xlsx") || fileName.endsWith(".XLSX"))
				version = "version2007";
			//生成excel工作空间
			Workbook hwb = null;
			if (version.equals("version2003")) {

				hwb = new HSSFWorkbook(stream);
			} else if (version.equals("version2007")) {
				hwb = new XSSFWorkbook(stream);
			}
			if(hwb==null)	return "生成Excel工作空间出错!";
			// 得到第一个工作表
			Sheet sheet = hwb.getSheetAt(0);
			if(sheet==null)	return "获取不到第一个工作表!";
			// 获取Excel的物理总行数
			int rowLength=sheet.getPhysicalNumberOfRows();
			//确保至少有2行数据
			if(rowLength<=1) return "请确保Excel至少有2行数据!";
			//确保最多有2000行数据
			if(rowLength>2000) return "请确保Excel最多有2000行数据!";
			//将第一行 作为列名定义行.
			Row row = sheet.getRow(0);
			if(row==null) return "Excel第一行读取失败!";
			//列名存储 在List中
			List<String> cells=new ArrayList<String>();
			//准备临时变量
			int i=0;String cellStr=null;Cell cell=null;
			//循环第一行
			while(true){
				cell=row.getCell(i++);
				if(cell==null) break;
				cellStr=getCellValue(cell);
				if(cellStr=="") break;
				cells.add(cellStr);
			}//读取列
			//遍历所有的行
			//使用StringBuffer拼接成SQL语句:Json对象
			String pici=UUID32.newGuid();
			StringBuffer sb=new StringBuffer("");
			int cellSize=cells.size();
			for(i=1;i<rowLength;i++){
				row=sheet.getRow(i);
				if(row==null) return "Excel:第"+i+"行为空,请确保Excel数据连续!";
				if(getCellValue(row.getCell(0)).equals("")) continue;//如果第一个列为空,就跳出
				sb.append("Insert into BS_IMP_COM(PICI,SORTNO,INPUT_DATE,CONTENT)")
				.append(" Values('")
				.append(pici)
				.append("',")
				.append(i)
				.append(",to_date('")
				.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()))
				.append("','yyyy-mm-dd hh24:mi:ss'),'{");
				for(int j=0;j<cellSize;j++){
					sb.append("\"")
					.append(cells.get(j))
					.append("\":\"")
					.append(getCellValue(row.getCell(j)))
					.append("\"")
					.append(j==cellSize-1?"":",");
				}//遍历列添加结束
				sb.append("}')");
				sql.add(sb.toString());
				sb.delete(0,sb.length());
			}//遍历行结束
			return "pici:"+pici;

		}catch(Exception e){
			e.printStackTrace();
			return e.getMessage();
		}
	}


	//	public static void main(String[] args) {
	//
	//		try {
	//			String url = "D:/1.xlsx";
	//			String tableName = "TEMP_IMPORTEXCEL";
	//			String[] clellName = { "H_ISBN", "H_NAME", "H_PRICE", "H_AMOUNT",
	//					"H_DISCOUNT" };
	//			int[] num = { 1, 2, 3, 4 };
	//			List<String> list = importEmployeeByPoi(url, tableName, clellName,
	//					num);
	//			System.out.println(list.get(0));
	//			System.out.println(list.get(1));
	//
	//		} catch (FileNotFoundException e) {
	//			// TODO Auto-generated catch block
	//			e.printStackTrace();
	//		}
	//
	//	}


    public static List<String> importEmployeeByPoiFC(InputStream stream,String url,
                                                     String tableName, List lists)
            throws FileNotFoundException {
        // 这里是解析出来的Excel的数据存放的List集合
        List<String> sqls = new ArrayList<String>();
        String version = "";
        try {
            if (url.endsWith(".xls"))
                version = "version2003";
            else if (url.endsWith(".xlsx"))
                version = "version2007";
            //测试
            Workbook hwb = null;
            if (version.equals("version2003")) {
                hwb = new HSSFWorkbook(stream);
            } else if (version.equals("version2007")) {
                hwb = new XSSFWorkbook(stream);
            }
            // 得到第一个工作表
            Sheet sheet = hwb.getSheetAt(0);
            Row row = null;
            // 日期格式化
            DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
            h_id_match=UUID32.newGuid();
            // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
            for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
                sheet = hwb.getSheetAt(i);
                List<String>nameList=new ArrayList<String>();
                // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
                for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                    row = sheet.getRow(j);
                    //取到商品信息的件册数
                    /***
                     * 如果导入的Excel数据汇中出现第一列的书号为空时，跳出读取excel的循环，不进行下面数据的读取（规则，excel中的数据不允许出现断行）
                     */
                    if(row.getCell(0)==null||"".equals(row.getCell(0).toString().trim())||"".equals(getCellValue(row.getCell(0)))){
                        break;
                    }
                    String h_isbn = getCellValue(row.getCell(0));
                    String sql = "insert into " + tableName + " ( h_inputdate ,PICI,SORT_NO,IS_PP,";
                    for (int n = 0; n < lists.size(); n++) {
                        sql += "" + lists.get(n) + "" + ",";
                    }
                    sql = sql.substring(0, sql.length() - 1);
                    sql += ") values ( '"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"','"+h_id_match+"','"+j+"','"+"0"+"',";
                    //获取行
                    if (j==0) {
                        for (int j2 = 0; j2 <8; j2++) {
                            if (row.getCell(j2)!=null||"".equals(row.getCell(j2))) {
                                nameList.add(getCellValue(row.getCell(j2)));
                            }else {
                                break;
                            }
                        }
                        continue;
                    }
                    for (int s = 0; s < lists.size(); s++) {
                        sql += "\'" + getCellValue(row.getCell(s))
                                + "\'" + ",";
                    }
                    sql = sql.substring(0, sql.length() - 1);
                    sql += ")";
                    System.out.println(sql);
                    // sql语句放入集合
                    sqls.add(sql);
                }
            }
        } catch (IOException e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return sqls;
    }



}
